#!/use/bin/python
# coding=utf-8
# 更新推送任务 弹窗率

import pymysql


from dbutils.pooled_db import PooledDB

# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf', port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',port=3306)  # 1为连接池里的最少连接数
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

#根据传入的SQL 返回执行SQL返回的数量
def selectNumBySql(sql,conf=2):
    if conf == 1:
        conn, cur = getConfConnection()
    else:
        conn, cur = getDataConnection()
    #print(sql)
    cur.execute(sql)
    numResult = cur.fetchone()
    num = 0
    if numResult is not None:
        num = numResult[0]
    return num

# 更新推送任务 弹窗率
def updateAlertRate():
    try:

        # 获取当日早间服务订阅数量
        conn, cur = getConfConnection()
        # 获取所有已下发的推送任务
        selectSql = 'select t.id from service_push_task t where t.push_status = 4 and t.id>13'
        cur.execute(selectSql)
        resultNum = cur.fetchall()
        for result in resultNum:
            taskId = result[0]
            #循环每个任务 获取每个任务的弹窗率
            alertRateSql = f"""select concat(tt.alertNum,'/',tt.allNum) alert_rate from (
            select (select count(1) from service_push_list p where p.task_id = t.id ) allNum,
                (select count(1) from service_push_list p where p.task_id = t.id and p.push_status = 4) alertNum from  service_push_task t where t.id = {taskId}) tt
            """
            alertRate = selectNumBySql(alertRateSql,1)
            updateSql = f"update service_push_task t set t.alert_rate = '{alertRate}' where t.id = {taskId}"
            print(f"id为{taskId},弹窗率为{alertRate}\n")
            cur.execute(updateSql)
            conn.commit()

    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    updateAlertRate()